using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using TienTeTuong;
using log4net;

namespace TienTeTuong
{
	/// <summary>
	/// Data access layer class for Sponsor
	/// </summary>
	class SponsorDAO 
	{
        /// <summary>
        /// Get list sponsor, Get all if limit = 0 else get list sponsors with limited
        /// </summary>
        /// <param name="limit"></param>
        /// <returns></returns>
        public List<SponsorDTO> GeListSponsor(int limit)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstSponsor= new List<SponsorDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM sponsor WHERE Active=1 ORDER BY CreatedDate ";
                if(limit!=0)
                {
                    strSQL += " LIMIT " + limit;
                }
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {

                        var sponsor = new SponsorDTO();
                        sponsor.ID = reader[0].ToString();
                        sponsor.FullName = reader[1].ToString();
                        sponsor.Email = reader[2].ToString();
                        sponsor.Address = reader[3].ToString();
                        sponsor.Phone = reader[4].ToString();
                        sponsor.Description = reader[5].ToString();
                        sponsor.Viplevel = int.Parse(reader[6].ToString());
                        sponsor.Money = reader[7].ToString();
                        sponsor.Active = reader[8].ToString() == "1";

                        lstSponsor.Add(sponsor);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstSponsor;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstSponsor;
            }
        }

        public List<SponsorDTO> GeListAllSponsor()
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstSponsor = new List<SponsorDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM sponsor ORDER BY CreatedDate ";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {

                        var sponsor = new SponsorDTO();
                        sponsor.ID = reader[0].ToString();
                        sponsor.FullName = reader[1].ToString();
                        sponsor.Email = reader[2].ToString();
                        sponsor.Address = reader[3].ToString();
                        sponsor.Phone = reader[4].ToString();
                        sponsor.Description = reader[5].ToString();
                        sponsor.Viplevel = int.Parse(reader[6].ToString());
                        sponsor.Money = reader[7].ToString();
                        sponsor.Active = reader[8].ToString() == "1";

                        lstSponsor.Add(sponsor);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstSponsor;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstSponsor;
            }
        }

        public SponsorDTO GetSponsorByID(string spID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;

            var sponsor = new SponsorDTO();
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM sponsor Where ID='" + spID + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    sponsor.ID = reader[0].ToString();
                    sponsor.FullName = reader[1].ToString();
                    sponsor.Email = reader[2].ToString();
                    sponsor.Address = reader[3].ToString();
                    sponsor.Phone = reader[4].ToString();
                    sponsor.Description = reader[5].ToString();
                    sponsor.Viplevel = int.Parse(reader[6].ToString());
                    sponsor.Money = reader[7].ToString();
                    sponsor.Active = reader[8].ToString() == "1";
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return sponsor;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return sponsor;
            }
        }
        public List<SponsorDTO> searchSponserByName(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<SponsorDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM sponsor where sponsor.FullName Like '%" + key + "%' Order by sponsor.FullName asc";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var sponsor = new SponsorDTO();

                        sponsor.ID = reader[0].ToString();
                        sponsor.FullName = reader[1].ToString();
                        sponsor.Email = reader[2].ToString();
                        sponsor.Address = reader[3].ToString();
                        sponsor.Phone = reader[4].ToString();
                        sponsor.Description = reader[5].ToString();
                        sponsor.Viplevel = int.Parse(reader[6].ToString());
                        sponsor.Money = reader[7].ToString();
                        sponsor.Active = reader[8].ToString() == "1";

                        lstCategory.Add(sponsor);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }
        public bool delete(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                //var strSQL = @"UPDATE `tientetuong`.`category` SET `Active` = b '0' WHERE `category`.`CateID` = '" + key + "'";
                var strSQL = @"Delete FROM `tientetuong`.`sponsor` where `sponsor`.`ID` = '" + key + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool update(SponsorDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"UPDATE `sponsor` SET  `FullName` =  '" + item.FullName + "', "
               + "`Email` =  '" + item.Email + "', "
               + "`Address` =  '" + item.Address + "', "
               + "`Phone` =  '" + item.Phone + "',"
               + "`Description` =  '" + item.Description + "',"
               + "`VipLevel` =  '" + item.Viplevel + "',"
               + "`Money` =  '" + item.Money + "',"
               + "`Active` =  b'" + (bool.Parse(item.Active.ToString()) ? "1" : "0") + "'"
               + " WHERE  `sponsor`.`ID` =  '" + item.ID + "';";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool add(SponsorDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"Insert into `Sponsor` (`ID`, `FullName`, `Email`, `Address`, `Phone`, `Description`, `VipLevel`, `Active`, `Money`)"
                    + " Values ('" + item.ID + "', '" + item.FullName + "', '" + item.Email + "', '" + item.Address + "', '" + item.Phone + "', '" + item.Description + "', '" + item.Viplevel + "', b'" + ((bool)item.Active ? "1" : "0") + "', '" + item.Money + "');";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

	}
}
